﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BSF.Db;
using BSF.Enums;
using HuntingFishGame.Domain.BLL.WeChat;
using HuntingFishGame.Domain.Model.WeChat;

namespace HuntingFishGame.Domain.DAL.WeChat
{
    public class WechatAccountBindDal
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="pubConn"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("Id", id);
                string strSql = "select count(1) from tb_WechatAccountBind where Id=@Id";
                return LibConvert.ObjToInt(pubConn.ExecuteScalar(strSql.ToString(), ps.ToParameters())) > 0;
            });
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public long Add(DbConn pubConn, WechatAccountBindModel model)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = @"insert into tb_WechatAccountBind(BatchNumber,OrderNumber,AccountNumber,UpdatePassword,OpenId,IsChecked,Status,StatusMemo,CreateTime,UpdateTime)
                                    values (@BatchNumber,@OrderNumber,@AccountNumber,@UpdatePassword,@OpenId,@IsChecked,@Status,@StatusMemo,@CreateTime,@UpdateTime);select @@IDENTITY";
                ps.Add("@BatchNumber", model.BatchNumber);
                ps.Add("@OrderNumber", model.OrderNumber);
                ps.Add("@AccountNumber", model.AccountNumber);
                ps.Add("@UpdatePassword", model.UpdatePassword);
                ps.Add("@OpenId", model.OpenId);
                ps.Add("@IsChecked", model.IsChecked);
                ps.Add("@Status", model.Status);
                ps.Add("@StatusMemo", "");
                ps.Add("@CreateTime", DateTime.Now);
                ps.Add("@UpdateTime", DateTime.Now);
                string id = pubConn.ExecuteScalar(strSql, ps.ToParameters()).ToString();
                return LibConvert.StrToInt64(id);
            });
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DbConn pubConn, WechatAccountBindModel model)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = "update tb_WechatAccountBind set IsChecked=@IsChecked,UpdateTime=@UpdateTime where OpenId=@OpenId and OrderNumber=@OrderNumber";
                ps.Add("@IsChecked", model.IsChecked);
                ps.Add("@UpdateTime", DateTime.Now);
                ps.Add("@OpenId", model.OpenId);
                ps.Add("@OrderNumber", model.OrderNumber);
                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        /// <summary>
        /// 更新状态
        /// </summary>
        public bool UpdateStatus(DbConn pubConn, long id, BSFAPICode status, string statusMemo = "")
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = "update tb_WechatAccountBind set Status=@Status,StatusMemo=@StatusMemo,UpdateTime=@UpdateTime where Id=@Id";
                ps.Add("@Status", (int)status);
                ps.Add("@StatusMemo", statusMemo);
                ps.Add("@UpdateTime", DateTime.Now);
                ps.Add("@Id", id);
                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = "delete from tb_WechatAccountBind where Id=@Id";
                ps.Add("@Id", id);
                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pubConn"></param>
        /// <param name="batchNumber"></param>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<WechatAccountBindModel> GetList(DbConn pubConn, string batchNumber, out int count, int pageIndex = 1, int pageSize = 10)
        {
            int _count = 0;
            List<WechatAccountBindModel> model = SqlHelper.Visit<List<WechatAccountBindModel>>(ps =>
            {
                string sqlwhere = "";
                StringBuilder sql = new StringBuilder();
                sql.Append("select ROW_NUMBER() over(order by T.ID desc) as rownum,T.* from tb_WechatAccountBind T where 1=1 ");
                if (!string.IsNullOrWhiteSpace(batchNumber))
                {
                    ps.Add("BatchNumber", batchNumber);
                    sqlwhere += " and ( T.BatchNumber =@BatchNumber )";
                }
                _count = Convert.ToInt32(pubConn.ExecuteScalar("select count(1) from tb_WechatAccountBind T where 1=1 " + sqlwhere, ps.ToParameters()));
                string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageSize * pageIndex;
                DataTable table = pubConn.SqlToDataTable(sqlSel, ps.ToParameters());
                List<WechatAccountBindModel> list = new List<WechatAccountBindModel>();
                foreach (DataRow dr in table.Rows)
                {
                    list.Add(WechatAccountBindModel.CreateModel(dr));
                }
                return list;
            });
            count = _count;
            return model;
        }

        /// <summary>获取详情</summary>
        /// <param name="pubConn"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public WechatAccountBindModel GetInfo(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("Id", id);
                string sql = "select * from tb_WechatAccountBind where Id=@Id";
                DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                if (table != null && table.Rows.Count > 0)
                {
                    return WechatAccountBindModel.CreateModel(table.Rows[0]);
                }
                else
                {
                    return null;
                }
            });
        }
        
        /// <summary>获取详情</summary>
        /// <param name="pubConn"></param>
        /// <param name="openId"></param>
        /// <returns></returns>
        public WechatAccountBindModel GetLastAccountBind(DbConn pubConn, string openId, string orderNumber)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("OpenId", openId);
                ps.Add("OrderNumber", orderNumber);
                string sql = "select top 1 * from tb_WechatAccountBind where OpenId=@OpenId and OrderNumber=@OrderNumber order by UpdateTime desc";
                DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                if (table != null && table.Rows.Count > 0)
                {
                    return WechatAccountBindModel.CreateModel(table.Rows[0]);
                }
                else
                {
                    return null;
                }
            });
        }

        
    }
}
